﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using EShop.Common;
using EShop.BussinessObjects;
using MySql.Data.MySqlClient;

namespace EShop.DataAccess
{
    public class CDAProduct
    {

        public static Boolean Delete(Product product, out string errMsg)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            errMsg = string.Empty;
            string _cmdText = " DELETE product " +
                              " WHERE ID = " + product.ID.ToString();
            try
            {
                SqlCommand cmd = new SqlCommand(_cmdText, con);
                cmd.ExecuteNonQuery();

                con.Close();
                return true;
            }
            catch (Exception e)
            {
                errMsg = e.Message;
                con.Close();
                return false;
            }
        }

        public static Boolean Update(Product product, out string errMsg)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            errMsg = string.Empty;
            string _cmdText = " UPDATE product SET " +
                              "  CompanyID = " + ((string.IsNullOrEmpty(product.Company.ID.ToString()) || (product.Company.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : product.Company.ID.ToString()) +
                              " ,Name = " + ((product.Name == null) ? "NULL" : "'" + product.Name.Trim() + "' ") +
                              " ,ProductCategoryID = " + ((string.IsNullOrEmpty(product.ProductCategory.ID.ToString()) || (product.ProductCategory.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : product.ProductCategory.ID.ToString()) +
                              " ,Price = " + ((string.IsNullOrEmpty(product.Price.ToString()) || (product.Price == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : product.Price.ToString().Replace(',', '.')) +
                              " ,Description = " + ((product.Description == null) ? "NULL" : "'" + product.Description.Trim() + "' ") +
                              " ,IsActive = " + ((product.IsActive == false) ? "0" : "1 ") +
                              " ,IsHappyHourActive = " + ((product.IsHappyHourActive == false) ? "0" : "1 ") +
                              " ,HappyHourDays = " + ((product.HappyHourDays == null) ? "NULL" : "'" + product.HappyHourDays.Trim() + "' ") +
                              " ,HappyHourFrom = " + (product.HappyHourFrom == null ? "NULL" : "'" + Convert.ToDateTime(product.HappyHourFrom).ToString() + "'") +
                              " ,HappyHourTo = " + (product.HappyHourTo == null ? "NULL" : "'" + Convert.ToDateTime(product.HappyHourTo).ToString() + "'") +
                              " ,HappyHourPrice = " + ((string.IsNullOrEmpty(product.HappyHourPrice.ToString()) || (product.HappyHourPrice == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : product.HappyHourPrice.ToString().Replace(',', '.')) +
                              " ,IsDiscount = " + ((product.IsDiscount == false) ? "0" : "1 ") +
                              " ,DiscountPrice = " + ((string.IsNullOrEmpty(product.DiscountPrice.ToString()) || (product.DiscountPrice == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : product.DiscountPrice.ToString().Replace(',', '.')) +
                              " WHERE ID = " + product.ID.ToString();
            try
            {
                SqlCommand cmd = new SqlCommand(_cmdText, con);
                cmd.ExecuteNonQuery();

                con.Close();
                return true;
            }
            catch (Exception e)
            {
                errMsg = e.Message;
                con.Close();
                return false;
            }
        }

        public static Boolean Insert(Product product, out string errMsg)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            errMsg = string.Empty;
            string _cmdText = " INSERT INTO contact (CompanyID, Name, ProductCategoryID, Price, Description, IsActive, IsHappyHourActive , HappyHourDays, HappyHourFrom, HappyHourTo, HappyHourPrice, IsDiscount, DiscountPrice) " +
                              " VALUES(" + ((string.IsNullOrEmpty(product.Company.ID.ToString()) || (product.Company.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : product.Company.ID.ToString()) +
                              " ," + ((product.Name == null) ? "NULL" : "'" + product.Name.Trim() + "' ") +
                              " ," + ((string.IsNullOrEmpty(product.ProductCategory.ID.ToString()) || (product.ProductCategory.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : product.ProductCategory.ID.ToString()) +
                              " ," + ((string.IsNullOrEmpty(product.Price.ToString()) || (product.Price == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : product.Price.ToString().Replace(',', '.')) +
                              " ," + ((product.IsActive == false) ? "0" : "1 ") +
                              " ," + ((product.IsHappyHourActive == false) ? "0" : "1 ") +
                              " ," + ((product.HappyHourDays == null) ? "NULL" : "'" + product.HappyHourDays.Trim() + "' ") +
                              " ," + (product.HappyHourFrom == null ? "NULL" : "'" + Convert.ToDateTime(product.HappyHourFrom).ToString() + "'") +
                              " ," + (product.HappyHourTo == null ? "NULL" : "'" + Convert.ToDateTime(product.HappyHourTo).ToString() + "'") +
                              " ," + ((string.IsNullOrEmpty(product.HappyHourPrice.ToString()) || (product.HappyHourPrice == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : product.HappyHourPrice.ToString().Replace(',', '.')) +
                              " ," + ((product.IsDiscount == false) ? "0" : "1 ") +
                              " ," + ((string.IsNullOrEmpty(product.DiscountPrice.ToString()) || (product.DiscountPrice == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : product.DiscountPrice.ToString().Replace(',', '.')) +
                              ")";

            
                              
            try
            {
                MySqlCommand cmd = new MySqlCommand(_cmdText, con);
                cmd.ExecuteNonQuery();

                _cmdText = " SELECT last_insert_id(); ";
                cmd = new MySqlCommand(_cmdText, con);
                int _id = Convert.ToInt32(cmd.ExecuteScalar());
                product.ID = _id;

                con.Close();
                return true;
            }
            catch (Exception e)
            {
                errMsg = e.Message;
                con.Close();
                return false;
            }
        }

        public static List<Product> Select(Product product, bool isFilterIsActive = false)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();

            string _cmdText =
            " SELECT p.* " +
            " FROM product p " +
            " WHERE ((p.ID = " + product.ID.ToString() + ") OR (" + product.ID.ToString() + " = " + ((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString() + ")) " +
            "   AND ((p.IsActive = " + Convert.ToInt32(product.IsActive).ToString() + ") OR (" + Convert.ToInt32(isFilterIsActive).ToString() + " = 0)) " +
            "ORDER BY p.ID";

            MySqlCommand cmd = new MySqlCommand(_cmdText, con);
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
            DataTable tbl = new DataTable();
            dataAdapter.Fill(tbl);

            List<Product> _productList = new List<Product>();

            foreach (DataRow row in tbl.Rows)
            {
                Product _product = new Product();

                _product.ID = (row["ID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ID"]));

                _product.Company = new Company();
                _product.Company.ID = (row["CompanyID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["CompanyID"]));

                _product.Name = (row["Name"].ToString() == "" ? null : Convert.ToString(row["Name"]));

                _product.ProductCategory = new ProductCategory();
                _product.ProductCategory.ID = (row["ProductCategoryID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ProductCategoryID"]));
                
                _product.Price = (row["Price"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToDecimal(row["Price"]));
                _product.Description = (row["Description"].ToString() == "" ? null : row["Description"].ToString());
                _product.IsActive = (row["IsActive"].ToString() == "1" ? true : false);
                _product.IsHappyHourActive = (row["IsHappyHourActive"].ToString() == "1" ? true : false);
                _product.HappyHourDays = (row["HappyHourDays"].ToString() == "" ? null : row["HappyHourDays"].ToString());
                if (row["HappyHourFrom"].ToString() != "")
                    _product.HappyHourFrom = Convert.ToDateTime("1999-01-01 " + row["HappyHourFrom"]);
                if (row["HappyHourTo"].ToString() != "")
                    _product.HappyHourTo = Convert.ToDateTime("1999-01-01 " + row["HappyHourTo"]);
                _product.HappyHourPrice = (row["HappyHourPrice"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToDecimal(row["HappyHourPrice"]));
                _product.IsDiscount = (row["IsDiscount"].ToString() == "1" ? true : false);
                _product.DiscountPrice = (row["DiscountPrice"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToDecimal(row["DiscountPrice"]));

                _productList.Add(_product);
            }

            con.Close();
            return _productList;
        }
    }
}